package org.dicker.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.dicker.dao.BillDao;
import org.dicker.entity.BillInfo;
import org.dicker.entity.BillSelector;
import org.dicker.util.ORMUtil;

public class BillDaoImpl implements BillDao {
	
	@Override
	public List<BillInfo> selectByUser(String open_id,BillSelector bs) {
		
		String sql = "select *"
				+ ",date_format(create_time, '%Y-%m-%d') create_time"
				+ ",type_type bill_type"
				+ " from tb_bill t1"
				+ " inner join tb_type t2 on t1.type_id=t2.type_id"
				+ " inner join tb_user t3 on t1.user_id=t3.user_id"
				+ " where t3.wx_openid=? ";
				
		
		List<Object> alist = new ArrayList<Object>();
		alist.add(open_id);
		sql = addToSql(sql, alist, bs);
		
		sql+= " order by create_time desc";
		
		List<BillInfo> list = ORMUtil.getInstance().selectList(BillInfo.class, sql,alist.toArray());
		return list;
	}
	@Override
	public List<BillInfo> selectByUserTypes(String open_id,String month, BillSelector bs) {
		
		String sql = 
				"select *,type_type bill_type from tb_type ta " + 
				"inner join " + 
				"(" + 
				"select t1.type_id,sum(amount) amount,type_type bill_type " + 
				"from tb_bill t1 " + 
				"inner join tb_type t2 on t1.type_id=t2.type_id " +
				"inner join tb_user t3 on t1.user_id=t3.user_id "
				+ "where t3.wx_openid=? " ;
		
		List<Object> alist = new ArrayList<Object>();
		alist.add(open_id);
		if(month!=null&&!month.isEmpty()) {
			sql+=" and date_format(create_time, '%Y-%m')=?";
			alist.add(month);
		}
		
		sql = addToSql(sql, alist, bs);
		
		sql +=	" group by t1.type_id" + 
				" ) tg on tg.type_id=ta.type_id "
				+ "order by amount desc";
		
		
		
		List<BillInfo> list = ORMUtil.getInstance().selectList(BillInfo.class, sql,alist.toArray());
		return list;
	}
	
	@Override
	public List<BillInfo> selectByUser(int user_id) {
		
		String sql = "select *"
				+ ",date_format(create_time, '%Y-%m-%d') create_time"
				+ ",type_type bill_type"
				+ " from tb_bill t1"
				+ " inner join tb_type t2 on t1.type_id=t2.type_id"
				+ " where user_id=?";
		List<BillInfo> list = ORMUtil.getInstance().selectList(BillInfo.class, sql,user_id);
		return list;
	}
	
	@Override
	public BillInfo selectOne(int bill_id) {
		
		String sql = "select *"
				+ ",date_format(create_time, '%Y-%m-%d') create_time"
				+ ",type_type bill_type"
				+ " from tb_bill t1"
				+ " inner join tb_type t2 on t1.type_id=t2.type_id"
				+ " where bill_id=?";
		List<BillInfo> list = ORMUtil.getInstance().selectList(BillInfo.class, sql,bill_id);
		return list.size()>0?list.get(0):null;
	}
	
	public List<BillInfo> selectAll(BillSelector bs){
		
		String sql = "select *"
				+ ",date_format(create_time, '%Y-%m-%d') create_time"
				+ ",type_type bill_type"
				+ " from tb_bill t1"
				+ " inner join tb_type t2 on t1.type_id=t2.type_id"
				+ " where 1=1";
				
		List<Object> alist = new ArrayList<Object>();
		
		sql = addToSql(sql, alist, bs)
				.replace("and type_id", "and t1.type_id");
		
		List<BillInfo> list = ORMUtil.getInstance().selectList(BillInfo.class, sql,alist.toArray());
		return list;
	}
	
	public int selectCount(BillSelector bs) {
		
		String sql = 
				"select count(*) num from tb_bill t1"
				+ " inner join tb_type t2 on t1.type_id=t2.type_id"
				+ " where 1=1 ";
		
		List<Object> alist = new ArrayList<Object>();
		
		sql = addToSql(sql, alist, bs);
		
		long i = (long) ORMUtil.getInstance().selectSingleValue("num", sql,alist.toArray());
		return (int)i;
	}
	
	
	/**通过查询器,添加sql条件,并给集合加入参数
	 * @return 处理后的sql*/
	public String addToSql(String sql,List<Object> alist,BillSelector bs) {
		
		if(bs!=null) {
			
			if(bs.getUser_id()!=-1) {
				sql+=" and user_id=?";
				alist.add(bs.getUser_id());
			}
			if(bs.getType_id()!=-1) {
				sql+=" and t1.type_id=?";
				alist.add(bs.getType_id());
			}
			if(bs.getBill_type()!=-1) {
				sql+=" and type_type=?";
				alist.add(bs.getBill_type());
			}
			if(bs.getMax_amount()!=-1) {
				sql+=" and amount<=?";
				alist.add(bs.getMax_amount());
			}
			if(bs.getMin_amount()!=-1) {
				sql+=" and amount>=?";
				alist.add(bs.getMin_amount());
			}
			String maxtime = bs.getMaxtime();
			if(maxtime!=null&&!maxtime.isEmpty()) {
				sql+=" and create_time <= ?";
				alist.add(maxtime);
			}
			String mintime = bs.getMintime();
			if(mintime!=null&&!mintime.isEmpty()) {
				sql+=" and create_time >= ?";
				alist.add(mintime);
			}
			String remark = bs.getRemark();
			if(remark!=null&&!remark.isEmpty()) {
				sql+=" and remark like ?";
				alist.add("%"+remark+"%");
			}
			if(bs.getStart()!=-1&&bs.getLength()!=-1) {
				sql+=" limit ?,?";
				alist.add(bs.getStart());
				alist.add(bs.getLength());
			}
		}
		return sql;
	}
	
	public boolean delete(String bill_ids) {
		
		String[] idsa = bill_ids.split(",");
		List<Integer> idsi = new ArrayList<Integer>();
		String idstr = "";
		for(String idsas:idsa) {
			idsi.add(Integer.valueOf(idsas));
			idstr+="?,";
		}
		idstr = idstr.substring(0,idstr.length()-1);
		String sql = "delete from tb_bill where bill_id in ("+idstr+")";
		int i = ORMUtil.getInstance().executeDML(sql,idsi.toArray());
		return i!=0;
	}
	
	
	public boolean add(BillInfo b) {
		
		List<Object> alist = new ArrayList<Object>();
		alist.add(b.getType_id());
		alist.add(b.getUser_id());
		alist.add(b.getAmount());
		alist.add(b.getRemark());
		alist.add(b.getCreate_time());
		
		String sql = "insert into "
				+ "tb_bill(type_id,user_id,amount,remark,create_time) "
				+ "values(?,?,?,?,?)";
		int i = ORMUtil.getInstance().executeDML(sql,alist.toArray());
		return i==1;
	}
	public boolean update(BillInfo b) {
		
		List<Object> alist = new ArrayList<Object>();
		alist.add(b.getType_id());
		alist.add(b.getUser_id());
		alist.add(b.getAmount());
		alist.add(b.getCreate_time());
		alist.add(b.getRemark());
		
		alist.add(b.getBill_id());
		
		String sql = "update tb_bill set "
				+ "type_id=?,user_id=?,amount=?,create_time=?,remark=? "
				+ "where bill_id=?";
		int i = ORMUtil.getInstance().executeDML(sql,alist.toArray());
		return i==1;
	}

	
	
}
